package gov.nhso.prov.acc.utils;

import gov.nhso.prov.acc.beans.Acc67ExpenseCategoryBean;
import gov.nhso.prov.acc.beans.Acc67IssueBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.beans.Acc6VenderBean;
import gov.nhso.prov.acc.core.DBConnection;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;

@SuppressWarnings("rawtypes")
public class ELUtil {
	
/*	public static String dateTh(String strDate) {
		String rtn = ""; // yyyy-MM-dd
		if (strDate.length() > 10) {
			DateUtil du = new DateUtil();
			strDate = du.convertDate("TS2D", strDate);
		}
    	String arrMonth[] = {"","ม.ค.","ก.พ.","มี.ค.","เม.ย.","พ.ค.","มิ.ย.","ก.ค.","ส.ค.","ก.ย.","ต.ค.","พ.ย.","ธ.ค."};
		String[] arrDate = strDate.split("-");
		if (arrDate.length == 3) {
			rtn = arrDate[2] + " " + arrMonth[Integer.parseInt(arrDate[1])] + " " + (Integer.parseInt(arrDate[0]) + 543);
		}
		return rtn;
	}*/	
	public static String dateTh(String strDate) {
		String rtn = ""; // yyyy-MM-dd
		if (strDate.length() > 10) {
			DateUtil du = new DateUtil();
			strDate = du.convertDate("TS2D", strDate);
		}
		String[] arrDate = strDate.split("-");
		if (arrDate.length == 3) {
			rtn = arrDate[2] + "/" + arrDate[1] + "/" + (Integer.parseInt(arrDate[0]) + 543);
		}
		return rtn;
	}
	
	public static String getMapValue(Map map, String key) {
		String data = "";
		if (map != null && map.get(key) != null) {
			data = map.get(key).toString();
		}
		return data;
	}
	public static String getResult(String num1, String num2) {
		double buffer = 0d;
		double number1 = 0d;
		double number2 = 0d;
		if(!num1.equals("") && num1 != null){
			number1 = Double.parseDouble(num1);
		}
		if(!num2.equals("") && num2 != null){
			number2 = Double.parseDouble(num2);
		}
		String result = "";
		buffer = number1 - number2;
		result = String.valueOf(buffer);
		return result;
	}
	
	public static String accountNoFormat(String accountNo) {
		if (accountNo.length() >= 10) {
			accountNo = accountNo.substring(0, 3) + "-" + accountNo.substring(3, 4) + "-" + accountNo.substring(4, 9) + "-" + accountNo.substring(9, 10);
		}
		return accountNo;
	}
	public static String removeQuote(String str) {
		str = str.replace("'", "");
		return str;
	}
	
	public static String getProvinceCodeByVenderId(String venderCode){
		Connection conn = null;
		ISUD isud = null;
		String data = "";
		try {
			conn = DBConnection.getConnection();
			isud = ISUDFactory.create(conn);
			
			Acc6VenderBean venderBean = new Acc6VenderBean();
			venderBean.setVenderCode("'"+venderCode+"'");
			venderBean = (Acc6VenderBean)isud.select(venderBean);
			data = venderBean.getProvinceId();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				isud = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return data;
	}
	public static String getUserStatus(String userStatus){
		String data = "";
		PropUtil propUtil = new PropUtil();
		data = propUtil.userStatus().get(userStatus);
		return data;
	}
	
	public static String getDataUtil(String type, String param) {
		Connection conn = null;
		ISUD isud = null;
		String data = "";
		try {
			conn = DBConnection.getConnection();
			isud = ISUDFactory.create(conn);
			if (!isud.chkData(param).equals("")) {
				DataUtil du = new DataUtil(isud);
				if(type.equals("ETA")){
					data = du.sumExpenseTransferAmount(param);
				} else if (type.equals("ECA")){
					data = du.sumExpenseChequeAmount(param);
				} else if (type.equals("AN")){
					data = du.getAllocateById(param);
				} else if (type.equals("VN")){
					data = du.getVenderNameById(param);
				} else if (type.equals("PAN")){
					data = du.getProvinceName(param);
				} else if (type.equals("CAT")){
					data = du.getCategoryById(param);
				} else if (type.equals("CAT7")){
					data = du.getCategoryAcc7ById(param);
				} else if (type.equals("SAM")){
					data = du.sumAmountByDocNo(param);
				} else if (type.equals("ROL")){
					data = du.getRoleName(param);
				} else if (type.equals("ALL")){
					data = du.getAllocateById(param);
				} else if (type.equals("PVI")){
					data = du.getProvinceName(param);
				} else if (type.equals("DAT")){
					DateUtil dateUtil = new DateUtil();
					data = dateUtil.changeFormatDate2DDMMYYYY(param);
				}
				conn.commit();
			}
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				isud = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return data;
	}
	
	public static List getExpenseCategory(String param, String mode) {
		Connection conn = null;
		ISUD isud = null;
		List list = new ArrayList();
		Acc67ExpenseCategoryBean eb = new Acc67ExpenseCategoryBean();
		try {
			conn = DBConnection.getConnection();
			isud = ISUDFactory.create(conn);
			if (!isud.chkData(param).equals("")) {
				if(mode.equals("getPrarentByCatId")){
					eb.setCategoryParent(param);
					eb.setCategoryAcc("7");
					eb.setCategoryApply("Y");
					list = isud.selectList(eb, " ORDER BY CATEGORY_SEQ ASC");
				} else if (mode.equals("getAll")){
					eb.setCategoryId(param);
					eb.setCategoryAcc("7");
					list = isud.selectList(eb, " OR CATEGORY_PARENT='"+param+"' AND CATEGORY_APPLY ='Y' ORDER BY CATEGORY_SEQ ASC");
					if (list.size() > 0){
						list.remove(0);
					}
				}
				conn.commit();
			} 
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				isud = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public static String getReasonDesc(String tsts) {
		Connection conn = null;
		ISUD isud = null;
		String data = "";
		try {
			if (tsts != null && !tsts.equals("")) {
				conn = DBConnection.getConnection();
				isud = ISUDFactory.create(conn);
				Acc6TransactionBean transactionBean = new Acc6TransactionBean();
				transactionBean.setTransactionTimestamp("TO_TIMESTAMP('" + tsts + "', 'YYYYMMDDHH24MISSFF3')");
				transactionBean = (Acc6TransactionBean) isud.select(transactionBean);
				if (!isud.chkData(transactionBean.getReasonCode()).equals("")) {
					PropUtil prop = new PropUtil();
					data = transactionBean.getReasonCode() + ":" + prop.reasonCode().get(transactionBean.getReasonCode());
				}
				conn.commit();
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				isud = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return data;
	}
	public static String statusActive(String active){
		String result = "";
		if (active.equals("Y")) {
			result = "ใช้งาน";
		} else if (active.equals("N")) {
			result = "ไม่ใช้งาน";
		}
		return result;
	}
	public static String getAmountByDocNo(String docNo, String catId) {
		Connection conn = null;
		ISUD isud = null;
		String data = "";
		try {
			conn = DBConnection.getConnection();
			isud = ISUDFactory.create(conn);
			if (docNo != null && !docNo.equals("")) {
				StringBuffer sb = new StringBuffer();
				sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS SUM_EXPENSE_AMOUNT ");
				sb.append("FROM ACC7_EXPENSE_DETAIL ");
				sb.append("WHERE DOC_NO = '"+docNo+"' AND CATEGORY_ID = '"+catId+"' AND  ACTIVE ='Y' ");
				data = isud.findDataSql(sb.toString()).get("SUM_EXPENSE_AMOUNT").toString();
			}
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				isud = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return data;
	}
	public static boolean isVenderUsed(String venderCode) {
		Connection conn = null;
		ISUD isud = null;
		boolean result = false;
		try {
			conn = DBConnection.getConnection();
			isud = ISUDFactory.create(conn);
			StringBuffer sb = new StringBuffer();
			int countVender = 0;

			sb.setLength(0);
			sb.append("SELECT NVL(COUNT(VENDER_CODE),0) AS COUNT_VENDER ");
			sb.append("FROM ACC6_EXPENSE_TRANSFER ");
			sb.append("WHERE VENDER_CODE = '"+venderCode+"' ");
			countVender += Integer.parseInt(isud.findDataSql(sb.toString()).get("COUNT_VENDER").toString());

			sb.setLength(0);
			sb.append("SELECT NVL(COUNT(VENDER_CODE),0) AS COUNT_VENDER ");
			sb.append("FROM ACC6_EXPENSE_CHEQUE ");
			sb.append("WHERE VENDER_CODE = '"+venderCode+"' ");
			countVender += Integer.parseInt(isud.findDataSql(sb.toString()).get("COUNT_VENDER").toString());

			sb.setLength(0);
			sb.append("SELECT NVL(COUNT(VENDER_CODE),0) AS COUNT_VENDER ");
			sb.append("FROM ACC6_CHEQUE_DETAIL ");
			sb.append("WHERE VENDER_CODE = '"+venderCode+"' ");
			countVender += Integer.parseInt(isud.findDataSql(sb.toString()).get("COUNT_VENDER").toString());
			
			if (countVender > 0) { // is used.
				result = true;
			}
			
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				isud = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	public static Acc67IssueBean getIssue(){
		Connection conn = null;
		ISUD isud = null;
		Acc67IssueBean aib = new Acc67IssueBean();
		try {
			conn = DBConnection.getConnection();
			isud = ISUDFactory.create(conn);
			aib.setIssueType("M");
			isud.select(aib);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				isud = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return aib;
	}
}